December 4, 2022

Maven Airlines Challenge

Full challenge details - Introducing the Maven Airlines Challenge.
Link to GitHub repository, including data and final output.

About the dataset

We're looking at airline satisfaction scores for 129,880 passengers.

Each record represents one passenger.

Records contain details about passenger demographics, flight distance and delays, travel class and purpose, and ratings for factors like cleanliness, comfort, and service, as well as overall satisfaction with the airline.

How to play the Maven Airlines Challenge

For this challenge, I assuming the role of Senior Data Analyst for Maven Airlines, a US-based airline headquartered in Boston, Massachusetts. The latest passenger survey results just came in and it looks like the satisfaction rate dipped under 50% for the first time ever. The leadership team needs to take action fast, so they've brought me in to analyze the data and find the key areas to focus on for getting back on track.

Import and transform data

Import the table into PowerBI, choose Transfrom data.

Create an additional column to categorise age groups.

Create an additional column to categorise travel distance (short/medium/long haul).

These steps are essential to breakdown the demographic of customers.

Duplicate the query and rename as rating; remove all columns except ID, the Criteria columns and the Satisfaction column.

Unpivot the Criteria columns, load data into the data model, connect the two tables via ID.

In the original table, I find it rather difficult to compare the overall rating of all the criteria. Hence, I created an additional table.

Visual design
The report template is divided into three sections. From left to right, the first section is a breakdown of customers based on the characteristic columns. This section provides an overview of the customers, also acting as slicer for the rest of the report through the cross filtering function. The second section summarises all the criteria based on their given scores. Cross checking the information in the first and second sections will reveal the linked between poor performing criteria and the relevant customer groups. From here, we can develop tailor solutions to address the bottom criteria. The third and final section looks at time delay to find out more about relationship between delay and satisfaction level.

Customer breakdown
Overall Satisfaction level
Visualisation - Card
Field: New Measure

Satisfaction Level = DIVIDE(
CALCULATE(
	COUNT(
		airline_passenger_satisfaction[Satisfaction]), 
		airline_passenger_satisfaction[Satisfaction] = "Satisfied")
	), 
	COUNT(airline_passenger_satisfaction[ID])
)

Survey Responses
Visualisation - Card
Filed: count of ID from satisfaction table

Customer Demographic
Visualisations - Multi row card
Fields: % of count of ID and the characteristic columns

By clicking on each customer group, we can view the group’s overall satisfaction level. This determine the focus when analysing the second section. Insights:
Customer Groups that have satisfaction score below average are:

Short-haul travelers (34%)

First-timers (24%)

Economy/Economy+ (20%)

Senior citizens (18%)

Young children (1-14) (14%)

Personal travelers (10%)

Business and Business Class travelers are those return high satisfaction scores. The airline may want to consider positioning deeper in the Business Travel segment with more perks and features catering for Business customers.
There was little difference in feedback from Male and Female customers.

Rating Summary
Visualisation - Stacked Bar Chart
Y-axis: Criteria column from rating table; X-axis: average of rating

The lowest performing criteria are Gate Location, Ease of Online Booking and In-flight Wifi Service. By using we cross filtering function with group breakdown in the first section, we can see that the least satisfied group of customers coincide with the bottom three criteria. These groups score below average in these criteria. Insights and suggestions:
Among Neutral/Dissatisfied customers, the lowest ranked criteria are:

Gate Location: While assigning gate is controlled by the airport, the airline can improve customer experience by providing clear instruction during check-in; or developing a real-time navigation app for airports.

Ease of Online Booking: This criterion coincides with low satisfaction score of customers above 65, which are probably not tech-savvy. Further investment to make the online booking portal more user-friendly is recommended.

In-flight Wifi Service: Young customers (24 and below) point to this criterion as the main pain point. As they are future customers with high online presence, it is crucial to prioritise addressing this matter.

Delay Time Analysis
Correlation between Departure Delay and Arrival Delay
Visualisation - Scatter chart
X-axis: Departure Delay; Y-axis: Arrival Delay.
Insights: Departure Delay time and Arrival Delay time are highly positively correlated. Thus, solving issues causing Departure Delays will also reduce Arrival Delays.

Average Delay Time
Visualisations - Card
Fields: average of Departure/Arrival Delay time

Delay and Satisfaction
Visualisation - Line chart
Y-axis: average of Departure delay time
Secondary Y-axis: average of Arrival delay time
Insights: The visualisations above demonstrate that, On average, the 15-minute mark is the turning point when customers are likely to become Neutral/Dissatisfied.

Departure/Arrival Delay histograms
Visualisations - Stacked Bar chart
X-axis: Departure/Arrival Delay
Y-axis: vount of Departure/Arrival Delay
The delay times are categorised into groups, each further group to the right represents an addition minute of delay time.

Flights within 15 minutes delay
Visualisations - Card
Fields: New measure:

% departure delay < 15 = DIVIDE(
	CALCULATE(
		count(airline_passenger_satisfaction[ID]),
		airline_passenger_satisfaction[Departure/Arrival Delay] <=15
	), 
	CALCULATE(count(airline_passenger_satisfaction[ID]))
)

Insight: About 77% of our flights depart/arrive within 15 minutes from the scheduled time. Better flight schedule program can help to improve this figure. We only Depart/Arrive on-time (zero delay) around 56% of all flights surveyed.
Departure/Arrival Time Convenience is the fourth lowest score. However, Benchmarking with prior performance measures and industry standard is required to determine if we are leading or trailing competitors. If we are not too far off, we should prioritise the lowest three criteria first.